package org.neuedu.dao;

import org.neuedu.bean.*;
import org.neuedu.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ArticleDaoImpl implements ArticleDao {
    @Override
    public int saveArticleInfo(Article article) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try{
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "INSERT into article(title,cid,uid,paykiss,content) VALUES(?,?,?,?,?)\n";
            ps = conn.prepareStatement(sql);
            ps.setString(1,article.getTitle());
            ps.setInt(2, article.getCid());
            ps.setInt(3,article.getUid());
            ps.setInt(4, article.getPayKiss());
            ps.setString(5,article.getContent());
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
}

    @Override
    public int updateKissNum(Article article) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try{
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update user set kissnum = kissnum - ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, article.getPayKiss());
            ps.setInt(2, article.getUid());
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public List<Article> getTopArticleList() {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select uid,avatar,nickname,catenameZh,title,DATE_FORMAT(publishtime,'%Y-%m-%d') publishtime,paykiss,isend,iscream,replynum,a.id aid\n" +
                    "from article a\n" +
                    "join user u\n" +
                    "on u.id = a.uid\n" +
                    "join category c\n" +
                    "on c.id = a.cid\n" +
                    "where istop = 1 order by publishtime desc";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Article article = new Article();
                article.setUid(rs.getInt("uid"));
                article.setCateNameZh(rs.getString("catenameZh"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setPayKiss(rs.getInt("paykiss"));
                article.setEnd(rs.getBoolean("isend"));
                article.setCream(rs.getBoolean("iscream"));
                article.setReplyNum(rs.getInt("replynum"));
                article.setId(rs.getInt("aid"));
                User user = new User();
                user.setId(rs.getInt("uid"));
                user.setAvatar(rs.getString("avatar"));
                user.setNickName(rs.getString("nickname"));
                article.setUser(user);
                list.add(article);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public List<Article> getTenMainArticleList(Integer tid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select uid,avatar,nickname,catenameZh,title,DATE_FORMAT(publishtime,'%Y-%m-%d') publishtime,paykiss,isend,iscream,replynum,a.id aid\n" +
                    "from article a\n" +
                    "join user u\n" +
                    "on u.id = a.uid\n" +
                    "join category c\n" +
                    "on c.id = a.cid\n" +
                    "where istop != 1 ";
            if(tid == 1){
                sql = sql + "and isend = 0 ";
            }else if(tid == 2){
                sql = sql + "and isend = 1 ";
            }else if(tid == 3){
                sql = sql + "and iscream = 1 ";
            }
            sql = sql + "order by DATE_FORMAT(publishtime,'%Y-%m-%d %H:%i:%s') desc limit 0,10 ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Article article = new Article();
                article.setUid(rs.getInt("uid"));
                article.setCateNameZh(rs.getString("catenameZh"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setPayKiss(rs.getInt("paykiss"));
                article.setEnd(rs.getBoolean("isend"));
                article.setCream(rs.getBoolean("iscream"));
                article.setReplyNum(rs.getInt("replynum"));
                article.setId(rs.getInt("aid"));
                User user = new User();
                user.setId(rs.getInt("uid"));
                user.setAvatar(rs.getString("avatar"));
                user.setNickName(rs.getString("nickname"));
                article.setUser(user);
                list.add(article);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public List<Article> getArticleByUid(Integer uid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select id,uid,title,replynum,publishtime,views \n" +
                    "from article \n" +
                    "where uid = ?" ;
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uid);

            rs = ps.executeQuery();
            while (rs.next()) {
                Article article = new Article();
                article.setUid(rs.getInt("uid"));
                article.setId(rs.getInt("id"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setViews(rs.getInt("views"));
                article.setReplyNum(rs.getInt("replynum"));
                list.add(article);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public List<reply> getReplyByUid(Integer uid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<reply> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select r.id,r.uid,aid,replyContent,title\n" +
                    "from reply r\n" +
                    "join article a\n" +
                    "on r.aid=a.id \n" +
                    "where r.uid=?" ;
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uid);

            rs = ps.executeQuery();
            while (rs.next()) {
                reply reply = new reply();
                reply.setReplyContent(rs.getString("replyContent"));
                reply.setId(rs.getInt("id"));
                reply.setAid(rs.getInt("aid"));
                reply.setUid(rs.getInt("uid"));
                Article article =new Article();
                article.setTitle(rs.getString("title"));
                reply.setArticle(article);
                list.add(reply);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public List<Article> CidmainArticleList(Integer cid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select uid,avatar,nickname,catenameZh,title,DATE_FORMAT(publishtime,'%Y-%m-%d') publishtime,isend,paykiss,iscream,replynum,a.id aid\n" +
                    "from article a\n" +
                    "join user u\n" +
                    "on u.id = a.uid\n" +
                    "join category c\n" +
                    "on c.id = a.cid where  istop != 1 ";

            if(cid == 1){
                sql = sql + "and cid  = 1 ";
            }else if(cid == 2){
                sql = sql + "and cid = 2 ";
            }else if(cid == 3){
                sql = sql + "and cid = 3 ";
            }else if(cid==4){
                sql =sql+"and cid = 4";
            }
            sql = sql + "order by DATE_FORMAT(publishtime,'%Y-%m-%d %H:%i:%s') desc limit 0,10 ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Article article = new Article();
                article.setUid(rs.getInt("uid"));
                article.setCateNameZh(rs.getString("catenameZh"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setPayKiss(rs.getInt("paykiss"));
                article.setEnd(rs.getBoolean("isend"));
                article.setCream(rs.getBoolean("iscream"));
                article.setReplyNum(rs.getInt("replynum"));
                article.setId(rs.getInt("aid"));
                User user = new User();
                user.setId(rs.getInt("uid"));
                user.setAvatar(rs.getString("avatar"));
                user.setNickName(rs.getString("nickname"));
                article.setUser(user);
                list.add(article);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public List<UserReply> getReplyUserList() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<UserReply> list = new ArrayList<>();
        try{
            conn = DBUtils.getInstance().getConnection();
            String sql = "select uid,nickname,avatar,count(*) counts " +
                    "from reply r join user u on u.id = uid " +
                    "where replytime between (current_date()-7) and sysdate() " +
                    "GROUP BY uid " +
                    "order by counts desc limit 0,12";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                UserReply info = new UserReply();
                info.setUid(rs.getInt("uid"));
                info.setNickName(rs.getString("nickname"));
                info.setAvatar(rs.getString("avatar"));
                info.setCounts(rs.getInt("counts"));
                list.add(info);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;

    }


    @Override
    public List<redgreadArticle> getredgreadArticle() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<redgreadArticle> list =new ArrayList<>();

        try{
            conn = DBUtils.getInstance().getConnection();
            String sql =  "select id,title,replynum from article order by replynum desc limit 0,10 ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                redgreadArticle info= new redgreadArticle();
                info.setId(rs.getInt("id"));
                info.setTitle(rs.getString("title"));
                info.setReplyNum(rs.getInt("replynum"));
                list.add(info);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;
    }

    @Override
    public Article getArticleById(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Article article = null;
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select a.id aid,title,cid,isend,istop,iscream,replynum,views,uid,paykiss,content,avatar,nickname,catenameZh,DATE_FORMAT(publishtime,'%Y-%m-%d') publishtime\n" +
                    "from article a\n" +
                    "join user u\n" +
                    "on u.id = a.uid\n" +
                    "join category c\n" +
                    "on c.id = a.cid\n" +
                    "where a.id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {
                article = new Article();
                article.setUid(rs.getInt("uid"));
                article.setCateNameZh(rs.getString("catenameZh"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setPayKiss(rs.getInt("paykiss"));
                article.setEnd(rs.getBoolean("isend"));
                article.setCream(rs.getBoolean("iscream"));
                article.setTop(rs.getBoolean("istop"));
                article.setReplyNum(rs.getInt("replynum"));
                article.setViews(rs.getInt("views"));
                article.setId(rs.getInt("aid"));
                article.setCid(rs.getInt("cid"));
                article.setContent(rs.getString("content"));
                User user = new User();
                user.setId(rs.getInt("uid"));
                user.setAvatar(rs.getString("avatar"));
                user.setNickName(rs.getString("nickname"));
                article.setUser(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return article;
    }



    @Override
    public List<reply> getReplyByAid(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<reply> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select uid,nickname,avatar,DATE_FORMAT(replytime,'%Y-%m-%d') replytime,replycontent,accepted,goodnum\n" +
                    "from user u\n" +
                    "join reply r\n" +
                    "on u.id = r.uid\n" +
                    "where r.aid = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {
                reply reply = new reply();
                reply.setReplyTime(rs.getString("replytime"));
                reply.setReplyContent(rs.getString("replycontent"));
                reply.setAccepted(rs.getBoolean("accepted"));
                reply.setAid(id);
                reply.setGoodNum(rs.getInt("goodnum"));
                User user = new User();
                user.setId(rs.getInt("uid"));
                user.setNickName(rs.getString("nickname"));
                user.setAvatar(rs.getString("avatar"));
                reply.setUser(user);
                list.add(reply);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);

        }
        return list;
    }

    @Override
    public int updateArticleViewsById(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtils.getInstance().getConnection();
            conn.setAutoCommit(false);
            String sql = "update article set views = views + 1 where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            count = ps.executeUpdate();
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return count;
    }

    @Override
    public List<Article> getArticleByCid(Integer uid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Article> list = new ArrayList<>();
        try {
            conn = DBUtils.getInstance().getConnection();
            String sql = "select id,uid,title,DATE_FORMAT(publishtime,'%Y-%m-%d %H:%i:%s') publishtime,views,replynum " +
                    "from article where cid = 1 and  " +
                    "uid = ?" ;
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uid);
            rs = ps.executeQuery();
            while (rs.next()) {
                Article article = new Article();
                article.setId(rs.getInt("id"));
                article.setUid(rs.getInt("uid"));
                article.setTitle(rs.getString("title"));
                article.setPublishTime(rs.getString("publishtime"));
                article.setViews(rs.getInt("views"));
                article.setReplyNum(rs.getInt("replynum"));
                list.add(article);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtils.getInstance().close(rs);
            DBUtils.getInstance().close(ps);
            DBUtils.getInstance().close(conn);
        }
        return list;

    }


}
